package com.hddf.project.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.LinkedList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelUtil {

	/**
	 * 
	 * @param file
	 * @param name
	 * @param tof
	 *            :true：只读表1
	 * @return
	 */
	public static List<List<String>> realExcel(File file, String name, boolean tof) {
		String suffix = name.substring(name.lastIndexOf(".") + 1);
		switch (suffix) {
		case "xls":
			return ExcelUtil.realXls(file, tof);
		case "xlsx":
			return ExcelUtil.realXlsx(file, tof);
		}
		return null;
	}

	@SuppressWarnings("deprecation")
	public static List<List<String>> realXls(File file, boolean tof) {
		List<List<String>> result = new LinkedList<List<String>>();
		try {
			InputStream is = new FileInputStream(file);
			HSSFWorkbook hwb = new HSSFWorkbook(is);
			FormulaEvaluator evaluator = hwb.getCreationHelper().createFormulaEvaluator();
			if (tof) {
				HSSFSheet hs = hwb.getSheetAt(0);
				if (null != hs) {
					for (int j = 0; j <= hs.getLastRowNum(); j++) {
						HSSFRow hr = hs.getRow(j);
						short minColIx = 0;
						short maxColIx = 0;
						if (null != hr) {
							minColIx = hr.getFirstCellNum();
							maxColIx = hr.getLastCellNum();
						}
						List<String> rowList = new LinkedList<String>();
						for (short colIx = minColIx; colIx < maxColIx; colIx++) {
							HSSFCell hc = hr.getCell(colIx);
							if (null == hc) {
								continue;
							}
							rowList.add(getHSSF(hc, evaluator));
						}
						result.add(rowList);
					}
				}
			} else {
				for (int i = 0; i < hwb.getNumberOfSheets(); i++) {
					HSSFSheet hs = hwb.getSheetAt(i);
					if (null == hs) {
						continue;
					}
					for (int j = 0; j <= hs.getLastRowNum(); j++) {
						HSSFRow hr = hs.getRow(j);
						short minColIx = 0;
						short maxColIx = 0;
						if (null != hr) {
							minColIx = hr.getFirstCellNum();
							maxColIx = hr.getLastCellNum();
						}
						List<String> rowList = new LinkedList<String>();
						for (short colIx = minColIx; colIx < maxColIx; colIx++) {
							HSSFCell hc = hr.getCell(colIx);
							if (null == hc) {
								continue;
							}
							rowList.add(getHSSF(hc, evaluator));
						}
						result.add(rowList);
					}
				}
			}
			is.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}

	public static List<List<String>> realXlsx(File file, boolean tof) {
		List<List<String>> result = new LinkedList<List<String>>();
		try {
			InputStream is = new FileInputStream(file);
			XSSFWorkbook xwb = new XSSFWorkbook(is);
			FormulaEvaluator evaluator = xwb.getCreationHelper().createFormulaEvaluator();
			if (tof) {
				XSSFSheet xs = xwb.getSheetAt(0);
				if (null != xs) {
					for (int j = 0; j <= xs.getLastRowNum(); j++) {
						XSSFRow xr = xs.getRow(j);
						short minColIx = 0;
						short maxColIx = 0;
						if (null != xr) {
							minColIx = xr.getFirstCellNum();
							maxColIx = xr.getLastCellNum();
						}
						List<String> rowList = new LinkedList<String>();
						for (short colIx = minColIx; colIx < maxColIx; colIx++) {
							XSSFCell xc = xr.getCell(colIx);
							if (null == xc) {
								continue;
							}
							rowList.add(getXSSF(xc, evaluator));
						}
						result.add(rowList);
					}
				}
			} else {
				for (int i = 0; i < xwb.getNumberOfSheets(); i++) {
					XSSFSheet xs = xwb.getSheetAt(i);
					if (null == xs) {
						continue;
					}
					for (int j = 0; j <= xs.getLastRowNum(); j++) {
						XSSFRow xr = xs.getRow(j);
						short minColIx = 0;
						short maxColIx = 0;
						if (null != xr) {
							minColIx = xr.getFirstCellNum();
							maxColIx = xr.getLastCellNum();
						}
						List<String> rowList = new LinkedList<String>();
						for (short colIx = minColIx; colIx < maxColIx; colIx++) {
							XSSFCell xc = xr.getCell(colIx);
							if (null == xc) {
								continue;
							}
							rowList.add(getXSSF(xc, evaluator));
						}
						result.add(rowList);
					}
				}
			}
			is.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}

	public static String getHSSF(HSSFCell hc, FormulaEvaluator evaluator) {
		switch (hc.getCellType()) {
		case HSSFCell.CELL_TYPE_BOOLEAN:
			return hc.getBooleanCellValue() ? "TRUE" : "FALSE";
		case HSSFCell.CELL_TYPE_FORMULA:
			try {
				evaluator.evaluateFormulaCell(hc);
				hc.setCellType(HSSFCell.CELL_TYPE_STRING);
				return hc.getStringCellValue();
			} catch (Exception e) {
				e.printStackTrace();
				return "";
			}
		case HSSFCell.CELL_TYPE_NUMERIC:
			if (HSSFDateUtil.isCellDateFormatted(hc)) {
				double d = hc.getNumericCellValue();
				Date date = HSSFDateUtil.getJavaDate(d);
				SimpleDateFormat dformat = new SimpleDateFormat("yyyy-MM-dd");
				return dformat.format(date);
			} else {
				hc.setCellType(HSSFCell.CELL_TYPE_STRING);
				return hc.getStringCellValue();
			}
		case HSSFCell.CELL_TYPE_STRING:
			return hc.getStringCellValue();
		default:
			return "";
		}
	}

	public static String getXSSF(XSSFCell xc, FormulaEvaluator evaluator) {
		switch (xc.getCellType()) {
		case HSSFCell.CELL_TYPE_BOOLEAN:
			return xc.getBooleanCellValue() ? "TRUE" : "FALSE";
		case HSSFCell.CELL_TYPE_FORMULA:
			try {
				evaluator.evaluateFormulaCell(xc);
				xc.setCellType(HSSFCell.CELL_TYPE_STRING);
				return xc.getStringCellValue();
			} catch (Exception e) {
				e.printStackTrace();
				return "";
			}
		case HSSFCell.CELL_TYPE_NUMERIC:
			if (HSSFDateUtil.isCellDateFormatted(xc)) {
				double d = xc.getNumericCellValue();
				Date date = HSSFDateUtil.getJavaDate(d);
				SimpleDateFormat dformat = new SimpleDateFormat("yyyy-MM-dd");
				return dformat.format(date);
			} else {
				xc.setCellType(HSSFCell.CELL_TYPE_STRING);
				return xc.getStringCellValue();
			}
		case HSSFCell.CELL_TYPE_STRING:
			return xc.getStringCellValue();
		default:
			return "";
		}
	}
	// public static void main(String[] args) {
	// List<List<String>> result = new LinkedList<List<String>>();
	// try {
	// InputStream is = new FileInputStream("F:/shexiangtou.xls");
	// HSSFWorkbook hwb = new HSSFWorkbook(is);
	// for (int i = 0; i < hwb.getNumberOfSheets(); i++) {
	// HSSFSheet hs = hwb.getSheetAt(i);
	// if (null == hs) {
	// continue;
	// }
	// for (int j = 0; j <= hs.getLastRowNum(); j++) {
	// HSSFRow hr = hs.getRow(j);
	// short minColIx = 0;
	// short maxColIx = 0;
	// if (null != hr) {
	// minColIx = hr.getFirstCellNum();
	// maxColIx = hr.getLastCellNum();
	// }
	// List<String> rowList = new LinkedList<String>();
	// for (short colIx = minColIx; colIx < maxColIx; colIx++) {
	// HSSFCell hc = hr.getCell(colIx);
	// if (null == hc) {
	// continue;
	// }
	// rowList.add(getStringVal(hc));
	// }
	// result.add(rowList);
	// }
	// }
	// } catch (Exception e) {
	// e.printStackTrace();
	// }
	// if (O.ckLt(result)) {
	// int num = 0;
	// for (List<String> list : result) {
	// System.out.println(list.get(0) + "     " + list.get(1));
	// if (num > 10) {
	// break;
	// }
	// num++;
	// }
	// }
	// }
}
